﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
//Tips : System.Data.Oracle命名必须在安装了Oracle客户端软件8.1.7或更高版本后才会存在，此扩展暂时没有添加

namespace WBasicClassLib.Helper
{
    /// <summary>
    /// 实用类，封装对数据库的操作，并且同时支持多种数据库
    /// </summary>
    public class DBHelper
    {
        #region 相关属性及成员

            #region 数据库连接对象及属性

            /// <summary>
            /// 数据库连接对象
            /// </summary>
            private IDbConnection _Connection;
            /// <summary>
            /// 数据库连接对象属性(只读)
            /// </summary>
            public IDbConnection Connection
            {
                get
                {
                    try
                    {
                        if (_Connection.State == ConnectionState.Closed)
                        {
                            _Connection.Open();
                        }
                        else if (_Connection.State == ConnectionState.Broken)
                        {
                            _Connection.Close();
                            _Connection.Open();
                        }

                        return _Connection;
                    }
                    catch (DataException ex)
                    {
                        throw new DataException("DBHelper Error : " + ex.Message);
                    }
                }
            }

            #endregion

            #region 用来描述传入的数据库对象的类型的枚举

            /// <summary>
            /// 用来描述传入的数据库对象的类型的枚举
            /// </summary>
            enum DbKind { SqlServer, OleDb, Odbc, NULL};

            #endregion

            #region 表示数据库类型的枚举

            private DbKind _DbKind = DbKind.NULL;

            #endregion

            #region 数据库相关对象生成工厂

            /// <summary>
            /// 数据库相关对象生成工厂
            /// </summary>
            private DBHelperCommonFactory _DbObjectFactory = null;

            #endregion

        #endregion

        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="Conn">传入已初始化的数据库连接对象(不关心有没有调用过Open)</param>
        public DBHelper(IDbConnection Conn)
        {
            try
            {
                if (Conn == null)
                {
                    throw new DataException("DBHelper Initializion Error : Conn is null");
                }
                else
                {
                    _Connection = Conn;

                    if (Conn is SqlConnection)
                    {
                        _DbKind = DbKind.SqlServer;
                        _DbObjectFactory = new DBHelperSqlServerFactory();
                    }
                    else if (Conn is OdbcConnection)
                    {
                        _DbKind = DbKind.Odbc;
                        _DbObjectFactory = new DBHelperODBCFactory();
                    }
                    else if (Conn is OleDbConnection)
                    {
                        _DbKind = DbKind.OleDb;
                        _DbObjectFactory = new DBHelperOleDbFactory();
                    }
                }
            }
            catch (DataException ex)
            {
                throw ex;
            }
        }

        #endregion

        #region 数据库相关操作

            #region 执行Sql语句（无参数）

            /// <summary>
            /// 执行Sql语句
            /// </summary>
            /// <param name="Sql">要执行的Sql语句</param>
            /// <returns>语句影响的记录条数</returns>
            public int ExecuteCommand(string Sql)
            {
                IDbCommand Cmd = _DbObjectFactory.GetCommand(Sql, Connection);
                int result = Cmd.ExecuteNonQuery();
                _Connection.Close();

                return result;
            }

            #endregion

            #region 执行Sql语句（有参数列表）

            /// <summary>
            /// 执行Sql语句(Override)
            /// </summary>
            /// <param name="Sql">要执行的Sql语句</param>
            /// <param name="Params">参数列表</param>
            /// <returns>语句影响的记录条数</returns>
            public int ExecuteCommand(string Sql, IDataParameter[] Params)
            {
                IDbCommand Cmd = _DbObjectFactory.GetCommand(Sql, Connection, Params);
                int result = Cmd.ExecuteNonQuery();
                _Connection.Close();

                return result;
            }

            #endregion

            #region 获得数据集（无参数Sql）

            /// <summary>
            /// 获得数据集
            /// </summary>
            /// <param name="Sql">要执行的Sql语句</param>
            /// <returns>执行Sql语句所获得的数据集</returns>
            public DataSet GetDataSet(string Sql)
            {
                IDbCommand Cmd = _DbObjectFactory.GetCommand(Sql, Connection);
                IDataAdapter Adapter = _DbObjectFactory.GetAdapter(Cmd);
                DataSet ds = new DataSet();
                Adapter.Fill(ds);
                _Connection.Close();

                return ds;
            }

            #endregion

            #region 获得数据集（有参数列表Sql）

            /// <summary>
            /// 获得数据集(Override)
            /// </summary>
            /// <param name="Sql">要执行的Sql语句</param>
            /// <param name="Params">参数列表</param>
            /// <returns>执行Sql语句所获得的数据集</returns>
            public DataSet GetDataSet(string Sql, IDataParameter[] Params)
            {
                IDbCommand Cmd = _DbObjectFactory.GetCommand(Sql, Connection, Params);
                IDataAdapter Adapter = _DbObjectFactory.GetAdapter(Cmd);
                DataSet ds = new DataSet();
                Adapter.Fill(ds);
                _Connection.Close();

                return ds;
            }

            #endregion

        #endregion

    }

    #region 数据库相关对象生成工厂系列

        #region 数据库工厂操作通用接口

        /// <summary>
        /// 数据库工厂操作接口
        /// </summary>
        internal interface DBHelperCommonFactory
        {
            IDbCommand GetCommand(string Sql, IDbConnection Conn);
            IDbCommand GetCommand(string Sql, IDbConnection Conn, IDataParameter[] Params);
            IDbDataAdapter GetAdapter(IDbCommand Cmd);
        }

        #endregion

        #region SqlServer使用的对象生成工厂

        /// <summary>
        /// SqlServer使用的对象生成工厂
        /// </summary>
        internal class DBHelperSqlServerFactory : DBHelperCommonFactory
        {

            #region DBHelperCommonFactory 成员

            public IDbCommand GetCommand(string Sql, IDbConnection Conn)
            {
                SqlConnection newConn = (SqlConnection)Conn;
                SqlCommand Cmd = new SqlCommand(Sql, newConn);
                
                return Cmd;
            }

            public IDbCommand GetCommand(string Sql, IDbConnection Conn, IDataParameter[] Params)
            {
                SqlConnection newConn = (SqlConnection)Conn;
                SqlCommand Cmd = new SqlCommand(Sql, newConn);
                Cmd.Parameters.AddRange(Params);

                return Cmd;
            }

            public IDbDataAdapter GetAdapter(IDbCommand Cmd)
            {
                SqlCommand newCmd = (SqlCommand)Cmd;
                SqlDataAdapter Adapter = new SqlDataAdapter(newCmd);

                return Adapter;
            }

            #endregion
        }

        #endregion

        #region OleDb使用的对象生成工厂

        internal class DBHelperOleDbFactory : DBHelperCommonFactory
        {
            #region DBHelperCommonFactory 成员

            public IDbCommand GetCommand(string Sql, IDbConnection Conn)
            {
                OleDbConnection newConn = (OleDbConnection)Conn;
                OleDbCommand Cmd = new OleDbCommand(Sql, newConn);

                return Cmd;
            }

            public IDbCommand GetCommand(string Sql, IDbConnection Conn, IDataParameter[] Params)
            {
                OleDbConnection newConn = (OleDbConnection)Conn;
                OleDbCommand Cmd = new OleDbCommand(Sql, newConn);
                Cmd.Parameters.AddRange(Params);

                return Cmd;
            }

            public IDbDataAdapter GetAdapter(IDbCommand Cmd)
            {
                OleDbCommand newCmd = (OleDbCommand)Cmd;
                OleDbDataAdapter Adapter = new OleDbDataAdapter(newCmd);

                return Adapter;
            }

            #endregion
        }

        #endregion

        #region ODBC使用的对象生成工厂

        internal class DBHelperODBCFactory : DBHelperCommonFactory
        {
            #region DBHelperCommonFactory 成员

            public IDbCommand GetCommand(string Sql, IDbConnection Conn)
            {
                OdbcConnection newConn = (OdbcConnection)Conn;
                OdbcCommand Cmd = new OdbcCommand(Sql, newConn);

                return Cmd;
            }

            public IDbCommand GetCommand(string Sql, IDbConnection Conn, IDataParameter[] Params)
            {
                OdbcConnection newConn = (OdbcConnection)Conn;
                OdbcCommand Cmd = new OdbcCommand(Sql, newConn);
                Cmd.Parameters.AddRange(Params);

                return Cmd;
            }

            public IDbDataAdapter GetAdapter(IDbCommand Cmd)
            {
                OdbcCommand newCmd = (OdbcCommand)Cmd;
                OdbcDataAdapter Adapter = new OdbcDataAdapter(newCmd);

                return Adapter;
            }

            #endregion
        }

        #endregion

    #endregion

}
